﻿using System;
using System.Collections.Generic;
using System.Web;
using hdbankwebservice.DTO;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;

namespace hdbankwebservice.DAO
{
    public class TaiKhoanKhachHangDAO
    {
        public static TaiKhoanKhachHang LayThongTinTaiKhoan(string sotaikhoan)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"SELECT *
                                FROM TAIKHOANKHACHHANG KH
                                WHERE (SOTAIKHOAN = @sotaikhoan) ";
            List<DbParameter> listparams = new List<DbParameter>();

            DbParameter para = dl.DoiSo();
            para.ParameterName = "sotaikhoan";
            para.DbType = DbType.String;
            para.Direction = ParameterDirection.Input;
            para.Value = sotaikhoan;
            listparams.Add(para);

            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql, listparams);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                TaiKhoanKhachHang user = new TaiKhoanKhachHang();
                user.MaTaiKhoanKhachHang = dt.Rows[0]["MATAIKHOANKHACHHANG"].ToString();
                user.SoTaiKhoan = dt.Rows[0]["SOTAIKHOAN"].ToString();
                user.SoTienGoc = (int)dt.Rows[0]["SOTIENGOC"];
                user.SoTinDungConLai = (int)dt.Rows[0]["SOTINDUNGCONLAI"];
                user.SoTinDungToiDa = (int)dt.Rows[0]["SOTINDUNGTOIDA"];
                user.MaThamSoTinDung = (int)dt.Rows[0]["MATHAMSOTINDUNG"];
                return user;
            }
        }
        public static bool CapNhatTaiKhoanKhachHang(TaiKhoanKhachHang kh)
        {
            string sql = string.Format("update TAIKHOANKHACHHANG set SOTIENGOC={0}, SOTINDUNGCONLAI = {1} where MATAIKHOANKHACHHANG ='{2}'", kh.SoTienGoc, kh.SoTinDungConLai, kh.MaTaiKhoanKhachHang);
            SqlCommand cmd = new SqlCommand(sql);
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            int i = dl.ThucThiKhongTruyVan(cmd);
            if (i == 1)
                return true;
            return false;
        }
        public static TaiKhoanKhachHang LayThongTinTaiKhoan(string sotaikhoan, string makhachhang)
        {
            string sqlString = @"SELECT * FROM TAIKHOANKHACHHANG TK, KHACHHANG KH WHERE KH.SOTAIKHOAN = TK.SOTAIKHOAN";
            if (sotaikhoan != "")
            {
                sqlString += @"  AND KH.SOTAIKHOAN = '" + sotaikhoan + "'";
            }
            else
            {
                if (makhachhang != "")
                {
                    sqlString += @"  AND KH.MAKHACHHANG = '" + makhachhang + "'";
                }
            }

            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql);
            if (dt.Rows.Count > 1)// vi cau truy van roi vao truong hop chon tat ca khi 2 tham so =""
            {
                return null;
            }
            else
            {
                TaiKhoanKhachHang user = new TaiKhoanKhachHang();
                user.MaTaiKhoanKhachHang = dt.Rows[0]["MATAIKHOANKHACHHANG"].ToString();
                user.SoTaiKhoan = dt.Rows[0]["SOTAIKHOAN"].ToString();
                user.SoTienGoc = (int)dt.Rows[0]["SOTIENGOC"];
                user.SoTinDungConLai = (int)dt.Rows[0]["SOTINDUNGCONLAI"];
                user.SoTinDungToiDa = (int)dt.Rows[0]["SOTINDUNGTOIDA"];
                user.MaThamSoTinDung = (int)dt.Rows[0]["MATHAMSOTINDUNG"];
                return user;
            }
        }

        public static int LayThongTinNhomKhachHang(string soTaiKhoan)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"select u.MANHOMKHACHHANG
                                from TAIKHOANKHACHHANG t, KHACHHANG k, USERS u
                                where t.SOTAIKHOAN = k.SOTAIKHOAN and k.MAKHACHHANG = u.MAKHACHHANG and t.SOTAIKHOAN = '" + soTaiKhoan + "'";
            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql);
            if (dt.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                return (int)dt.Rows[0]["MANHOMKHACHHANG"];
            }
        }
    }
}